1. Field of the Invention
The present invention is directed to the field of database report generation and data mining. It is specifically related to retrieval of data from a database over the Internet and presenting the results as web pages. The data to be retrieved primarily include historical reports for individual remote assets or vehicles and fleet summaries consisting of location and telemetry data from vehicles collected in real time through a wireless data network. Flexible database queries are generated and efficiently displayed in a web browser using ASP (Active Server Page), XML (Extensible Markup Language), XSL (Extensible Stylesheet Language) and COM (Component Object Model) technologies.
2. Brief Review of the Prior Art
Data processing systems in business enterprises are typically implemented in a three tiered client-server system, the tiers consisting of a database, business logic run on the server to access the database, and a presentation component to allow entry or display of the data. Widely distributed businesses often use the Internet to connect the client applications to the server. Browser based clients are only recently coming into use and typically provide less functionality to the end user than do full client applications. This is because most display processing and formatting occurs on the server, which slows the interface and restricts functionality.
Common examples of browser-based database access systems are Internet search engines such as Yahoo!(trademark) (trademark of Yahoo!, Inc.) or on-line product catalogs or auctions such as eBay(trademark) (trademark of eBay, Inc.). These systems provide the user with the ability to enter simple, Boolean database queries and return the search results in list form on a static HTML (Hypertext Markup Language) web page. The user cannot modify the view of the data. Additionally, these applications are consumer oriented and contain no specific business logic to apply to the database queries or report formatting. However, they may present additional information, such as advertising, to the user based on search terms included in the query.
An automatic report generating system disclosed by Solecki in U.S. Pat. No. 6,055,541 uses ASP (Active Server Pages), a web server scripting technology, to create H reports that are then displayed in the browser. The system generates engineering reports from a database of parameters of parts from a computer aided design application. The ASP script launches the code necessary to retrieve data from the database and format an HTML page according to a predefined format. If the user desires any change to the report format, a new report must be generated by the server.
Data mining is the process of examining a large database of information to identify trends in particular parameters or to discover new relationships between parameters. Apte discloses in U.S. Pat. No. 5,970,464 a client-server based system for mining insurance data to analyze underwriting profitability. A web based data mining system is disclosed by Appleman in U.S. Pat. No. 5,918,010. This system creates HTML based web pages by accessing information from multiple web pages to combine relevant data onto a single page.
While there are many systems for retrieving data from remote vehicles, there is no mention of database reporting systems to extract those data for reporting business related information. In U.S. Pat. No. 5,844,473, Kaman describes a system for collecting usage data, such as mileage, from vehicles and storing the data in a central computer for later use in determining vehicle maintenance schedules. However, the system does not seem to provide detailed reports on vehicle location or activity related to the business in which those vehicle operate. In U.S. Pat. No. 5,777,580, Janky references numerous systems for obtaining location and telemetry data from remote vehicles and assets over wireless networks.
Travel and route planning is a field that is the counterpart to historical reporting of vehicle location and telemetry data. DeLorme, in U.S. Pat. No. 5,802,492, describes a stand-alone travel planning application that identifies the locations of points of interest and events or activities to be performed at those points of interest. The application provides the user with a list of locations to be visited and multimedia presentations related to those locations. It does provide for the loading of an actual travel route into the application for use in creating new travel plans.
Many businesses operate fleets of vehicles, have a mobile workforce, have remote fixed assets, or rent mobile equipment. Managing these assets or employees efficiently is a difficult task. Getting data from these assets that indicates what they are doing and where and when they are doing it into applications that managers use to run their businesses is of critical importance.
In the aforementioned ""850 application, Coffee et. al. disclose a Location Aware Wireless Gateway that is a platform for providing location and telemetry data from remote assets to business end users and applications. Applications and data are provided to customers over the Internet from the network operations center. Text messaging and mapping are provided as core functions. Standard protocol interfaces are provided to the core location business logic and database as well as the mapping and messaging to enable other business applications to use the wireless networks and location information. One example of location aware business logic is the ability to send a location of interest to a mobile device. The mobile device then can automatically report when it has arrived or left the location. The ability to send locations and receive status is extended to any application, such as dispatching, to enable it to track work order status. Interfaces to mapping functions allow the application to view and define locations of interest.
The Gateway stores asset telemetry and location data in a large database for later reporting or playback. Businesses require detailed reports on vehicle and asset activities to settle disputes, provide data for cost determination and billing, and to evaluate asset utilization. With a large enough body of data, businesses can perform data mining functions to determine trends in vehicle trip times or distances, costs of and revenues generated by assets, and other information to enhance business decision making. The Internet and standard browser software allow the business user to access data at any time from almost anywhere. The subject of the present invention is a customizable, web based, reporting and data mining system that provides these benefits to business owners with vehicle fleets and remote personnel or assets.
The database reporting and data mining system that is the subject of this invention is implemented, in one of its aspects, as part of a Location Aware Wireless Gateway. The Gateway provides a standard platform for providing location and telemetry data through wireless connections from remote assets to business end users and applications. Applications and data are provided to customers over the Internet from the network operations center. The Gateway provides inherent text messaging and mapping applications. Telemetry data, location data, and message text are stored in the database within the Gateway. The database reporting system is provided as a mechanism to analyze the past activity of a business""s assets or vehicles.
Remote assets are connected to the Gateway through wireless data networks; data reported by the assets are stored in the Gateway database and forwarded in real time through the Internet to any business users who are owners of those assets. Assets may be vehicles, remote equipment, or personnel. Assets report periodic location related information (position, speed, direction of travel, etc.) as well as other telemetry information as required. Telemetry data include events related to changes in sensor inputs, such as a door opening or ignition turning on, and other navigation related events, such as stops, or arrivals at and departures from specific sites of interest. The location aware business logic residing in the Gateway and the remote assets, tags events with location information. The location information is both a set of coordinates and a work site identifier that indicates where an event took place. Work sites identify the locations of jobs or other points of interest and are part of the SITE DISPATCH(trademark) (trademark of Grid Data, Inc., assignee of this application) business logic in the Gateway.
The reporting system presents the user with a web based interface to the Gateway database. The system produces event reports for each of the user""s assets. Event reports are simply time and location tagged lists of telemetry events reported by the asset. The user is able to configure custom reports to logically group events of interest to the user. The user may be interested in time and distance between particular locations or time vehicles spend idling versus moving. Arrival and departure events or moving and stopping indications are set as the appropriate start and end events for a group, and the report configuration is stored. The new report, when executed, will extract the times, and distance when appropriate, between the start and end events and presents those in list or chart form. Multiple groups can be presented in a single report, and the report can be run across multiple vehicles or long time periods to examine trends.
The invention uses a variety of Internet and database technologies to make web based reporting and data mining possible. These technologies include ASP, XML and XSL, DHTML (Dynamic Hypertext Markup Language), COM, and ADO (ActiveX Data Objects).
Active Server Pages are processed on the server and allow the server to create HTML pages dynamically. This allows the report generator to vary report parameters to create many reports using a small set of pages and gives the pages direct access to the database through ADO. ADO are a standard protocol for accessing data in a database. Using this technology, the system is able to access any database that supports ADO, including Oracle(trademark) (trademark of Oracle Corporation) and Microsoft(trademark) (trademark of Microsoft Corporation) SQL Server. In the preferred embodiment of the invention an Oracle database is used to store all event data for which reports are to be generated. Stored Procedures and Oracle""s PL-SQL language allow for fast, powerful queries of the report data. This database also has a partitioning capability which allows data to be partitioned by date, customer or other parameter; this, in turn, allows for faster queries of data tables because a smaller portion of the database may be searched for many queries.
COM is used to create distributed and concurrent systems for modern Microsoft(trademark) operating systems. The report COM components apply business logic to the report parameters and call the appropriate database stored procedures to get a recordset from the database. The recordset is then converted by the COM components and returned to the calling web page in XML format. Each web server has a copy of the report COM components which query the database. This provides redundancy in the web server implementation by allowing users to connect to and use any available web server for generating reports even if one or more are offline. It also allows for load balancing, so the web servers are distributing the report generation workload.
XML is a powerful, easy to use method to transfer data between web pages, and report data is provided to the client browser in XML format. The presentation of XML on the screen is controlled by the use of XSL. It is used to modify the presentation of the XML data without having to access the web server. For example, the user can change from a text based report to a graphical chart of the same data. The reports are shown on web pages with DHTML content. Dynamic HTML embeds code in a normal static HTML page to provide the user a richer experience when selecting, configuring and interacting with the report. Once a report is generated, the user can interact with the report to get more detailed information or a different view of the report.
The present invention has several attendant advantages and features for the business user, including providing the user with a sophisticated data mining capability.
One advantage is Active Report Pages. When the user generates an event report, each event has icons beside it which allow for further interaction with the report. For example, the user may be looking at a xe2x80x9cSpeeding Startedxe2x80x9d event on the report. If the interest is in seeing where the asset was located when the xe2x80x9cSpeeding Startedxe2x80x9d occurred, the user simply clicks on the map icon beside the event, which triggers the generation of a map showing the vehicle""s location at the time the event occurred. As another example, if the report page shows that an employee of the user is located at one of the defined sites of interest, the user can identify that location by simply moving the mouse over the site icon and the name of the site is displayed as a tooltip.
Another advantage is that queries are generated based on events. Each type of event that can occur has a unique ID number. When the user is interested in a subset of events, he can create a custom report in which the user selects the events to be included in the report. This allows for very quick database queries of the desired data and fast parsing of that data. The COM component that converts the database recordset to XML does not have to parse through every event and discard the unused events.
Still another advantage is that the invention supports different types of events based on asset types defined in the database. Business owners typically have different types of assets about which they need information. The events that are triggered by one asset type do not always match the events that are triggered by another asset type. The report generator allows selection of events that are only relevant to the desired asset types. For example, if a business has concrete trucks and dump trucks, it can track when the concrete trucks pour cement and when the dump trucks unload material in the same report. The report generator only displays the events relative to each asset type when the report is created by the user.
Yet another advantage of the invention is that events in reports can be grouped, and time durations and distances of business importance are automatically calculated. A group can be created with any two events; a start event and an end event. A group provides the user with time and mileage between events, where the assets of interest are vehicles. The group totals can then be summed for a vehicle and across the entire fleet of vehicles, providing the user with valuable business information. The grouping feature gives the user the ability to customize reports to fit his business needs. Group types allow the user to report on all groups, the first group of the selected time range, or the last group of the selected time range. Event types allow the user to use all events, the first or the last event only. For example, the user could define a group called xe2x80x9cHours Workedxe2x80x9d and select the start event as xe2x80x9cFirstxe2x80x94Ignition Onxe2x80x9d and the end event as xe2x80x9cLastxe2x80x94Ignition Offxe2x80x9d. Time offsets enable the user to adjust the group total time by a specified amount of time. Expanding on the previous example, the user could have the report generator add 15 minutes to Total Hours Worked for each employee. Group Values allow the user to multiply the group total time by an associated value such as a dollar amount.
Another advantage of the invention is that reports can be reformatted and customized using XSL stylesheets. With XSL, the user can see the full report, hide pieces of information, switch between different views of the data such as text and charts, all without returning to the server for more information. As long as the data stay the same, stylesheets allow the user to reformat the display of that information on their computer. Further customization of the reports is available through a xe2x80x9cMy Reportsxe2x80x9d configuration. After the user has customized the reports for his business needs, a set of reports can be created with one mouse click. The user can select the default view, display of the company logo, and the relative date/time range for which to run the report. Each report in the set has its own set of parameters. The user can also select the number of columns in which to display the set of reports.
Still another advantage is that the invention provides data mining functions that compute summaries and trends of data. Fleet summaries and trend reporting allow the user to see the data as an aggregate of all the vehicles in is fleet. Business costs, averages, cycle times, trends, efficiency and productivity can be determined. The discrete events which occur in daily activities of the vehicles are translated into valuable information to aid business decisions.
The gateway is only one example of a data source for the invention, the gateway also providing security. It should be understood, then, that the reporting system and method of the invention with groups and web/XML/XSL features can be used in any application that requires web distributed access to the reports. The invention is especially useful in applications that can make use of groups, e.g., process control or manufacturing where items or workers spend time at different stations on certain tasks (location and time component). Events such as xe2x80x9cstart weldingxe2x80x9d and xe2x80x9cend weldingxe2x80x9d, or xe2x80x9cstart paintingxe2x80x9d and xe2x80x9cend paintingxe2x80x9d can go into a database that uses the system for statistical process control, and so forth. Key features of the invention include XML/XSL; groupsxe2x86x92summaries, trends, charts; and active report pages.
A web-based database method is used for reporting performance data in carrying out a web-served application of a business enterprise to business enterprise users on web browsers through multiple wireless networks and the Internet. Business logic is used to receive data related to actual and projected performance of the application as detected or determined by one or more performance sensors at locations where the application is being performed. The data is used to tag events of interest toward an assessment of performance, and the tagged events are stored in a database for retrieval thereof in real time through the Internet by the respective business enterprise users. Web servers responsible for performing reporting functions are integrated into a wireless network for access to the web. Groups of database-stored events related by event type are established, and data derived therefrom is reported from the web servers in XML to the web browsers in response to queries from users. The reported XML data may be re-formatted in XSL through the web browser for viewing on a web page.